\c contact contact

CREATE TABLE PERSON_TYPE (
    ID SERIAL NOT NULL,  
    NAME varchar(50) not null,
	LAST_UPDATED TIMESTAMP NOT NULL DEFAULT NOW(),
	LAST_UPDATE_USER VARCHAR(255) NOT NULL DEFAULT 'SYSTEM',
	CREATED TIMESTAMP NOT NULL DEFAULT NOW(),
	CREATE_USER VARCHAR(255) NOT NULL DEFAULT 'SYSTEM',
    CONSTRAINT IDX_PERSON_TYPE_ID PRIMARY KEY (ID)
);

CREATE TABLE PERSON (
    ID SERIAL NOT NULL,  
    FIRST_NAME varchar(50) not null,
    LAST_NAME varchar(50) not null,
    TYPE integer, 
    LOCK_VERSION INTEGER NOT NULL DEFAULT 1,
	LAST_UPDATED TIMESTAMP NOT NULL DEFAULT NOW(),
	LAST_UPDATE_USER VARCHAR(255) NOT NULL DEFAULT 'SYSTEM',
	CREATED TIMESTAMP NOT NULL DEFAULT NOW(),
	CREATE_USER VARCHAR(255) NOT NULL DEFAULT 'SYSTEM',
    CONSTRAINT IDX_PERSON_ID PRIMARY KEY (ID),
    CONSTRAINT FK_PERSON_TYPE FOREIGN KEY (TYPE) REFERENCES PERSON_TYPE(ID)
);

CREATE TABLE PERSON_STUDENT (
    ID integer not null,  
    SCHOOL_NAME varchar(50), -- not null,
    CONSTRAINT FK_PERSON_STUDENT_ID FOREIGN KEY (ID) REFERENCES PERSON(ID) on delete cascade
);

CREATE TABLE PERSON_PROFESSIONAL (
    ID integer not null,  
    COMPANY_NAME varchar(50) not null,
    CONSTRAINT FK_PERSON_PROFESSIONAL_ID FOREIGN KEY (ID) REFERENCES PERSON(ID) on delete cascade
);

CREATE TABLE ADDRESS (
    ID SERIAL NOT NULL,
    PERSON_ID integer,  
    ADDRESS varchar(255),
    CITY varchar(50) not null,
    STATE varchar(50) null,
    ZIP_POSTAL varchar(30) not null,
    COUNTRY varchar(50) not null,
    LOCK_VERSION INTEGER NOT NULL DEFAULT 1,
	LAST_UPDATED TIMESTAMP NOT NULL DEFAULT NOW(),
	LAST_UPDATE_USER VARCHAR(255) NOT NULL DEFAULT 'SYSTEM',
	CREATED TIMESTAMP NOT NULL DEFAULT NOW(),
	CREATE_USER VARCHAR(255) NOT NULL DEFAULT 'SYSTEM',
    CONSTRAINT IDX_ADDRESS_ID PRIMARY KEY (ID),
    CONSTRAINT FK_ADDRESS_PERSON_ID FOREIGN KEY (PERSON_ID) REFERENCES PERSON(ID) on delete cascade
);

INSERT INTO PERSON_TYPE(ID, NAME) 
   VALUES(1, 'STUDENT');
INSERT INTO PERSON_TYPE(ID, NAME) 
   VALUES(2, 'PROFESSIONAL');
